Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# PERCENTAGE

The PERCENTAGE function computes the percent of total for each value in a numeric expression.

Return Value

DECIMAL

Syntax

PERCENTAGE(expression [BASEDON dimension-list])

Arguments

expression

  The numeric expression for which percent figures are to be computed.

BASEDON dimension-list

  An optional list of one or more of the dimensions of expression on which to base the percentage for each value. When you do not specify the dimensions, then PERCENTAGE bases the percentage on the total of all of the values of all of the dimensions of expression.

Notes

The Effect of NASKIP on PERCENTAGE

PERCENTAGE is affected by the NASKIP option. When NASKIP is set to `YES` (the default), then PERCENTAGE ignores `NA` values. When NASKIP is set to `NO`, then PERCENTAGE returns `NA` for any cell in expression whose value is `NA`.

Examples

Example 19-28 Calculating the Percentage

The following statements s limit the `month` and `district` dimensions, and report the data values, with subtotals, for the `units` variable.

    LIMIT month TO 'Jul96' TO 'Sep96'
    LIMIT district TO 'Denver'
    REPORT SUBTOTALS W 8 units

The preceding statement produces the following output.

    DISTRICT: DENVER
                   ----------UNITS-----------
                   ----------MONTH-----------
    PRODUCT         Jul96    Aug96    Sep96
    -------------- -------- -------- --------
    Tents               608      517      441
    Canoes              467      363      411
    Racquets          3,006    2,836    2,838
    Sportswear        2,395    2,039    2,138
    Footwear          1,581    1,532    1,667
    -------------- -------- -------- --------
    TOTAL DENVER      8,057    7,287    7,495

This statement reports the percentage that each `month` value represents of the total `month` values for each of the `product` values that are in status. The total of the values that PERCENTAGE returns for each `product` value is `1`.

    REPORT SUBTOTALS W 8 DOWN month PERCENTAGE(units BASEDON month)

The preceding statement produces the following output.

    DISTRICT: DENVER
             -----------PERCENTAGE(UNITS BASEDON MONTH)------------
             -----------------------PRODUCT------------------------
    MONTH      Tents      Canoes    Racquets  Sportswear  Footwear
    -------- ---------- ---------- ---------- ---------- ----------
    Jul96          0.39       0.38       0.35       0.36       0.33
    Aug96          0.33       0.29       0.33       0.31       0.32
    Sep96          0.28       0.33       0.33       0.33       0.35
    -------- ---------- ---------- ---------- ---------- ----------
    TOTAL          1.00       1.00       1.00       1.00       1.00
    DENVER

This statement reports the percentage that each `product` value represents of the total `product` values for each of the `month` values that are in status.

    REPORT SUBTOTALS W 8 PERCENTAGE(units BASEDON product)

The preceding statement produces the following output.

    DISTRICT: DENVER
                   -PERCENTAGE(UNITS BASEDON-
                   ---------PRODUCT)---------
                   ----------MONTH-----------
    PRODUCT         Jul96    Aug96    Sep96
    -------------- -------- -------- --------
    Tents              0.08     0.07     0.06
    Canoes             0.06     0.05     0.05
    Racquets           0.37     0.39     0.38
    Sportswear         0.30     0.28     0.29
    Footwear           0.20     0.21     0.22
    -------------- -------- -------- --------
    TOTAL DENVER       1.00     1.00     1.00

This statement reports the percentage based on all of the dimensions of the `units` variable. The total of all of the values that PERCENTAGE returns is `1`.

    REPORT SUBTOTALS W 8 PERCENTAGE(units)

The preceding statement produces the following output.

    DISTRICT: DENVER
                   ----PERCENTAGE(UNITS)-----
                   ----------MONTH-----------
    PRODUCT         Jul96    Aug96    Sep96
    -------------- -------- -------- --------
    Tents              0.03     0.02     0.02
    Canoes             0.02     0.02     0.02
    Racquets           0.13     0.12     0.12
    Sportswear         0.10     0.09     0.09
    Footwear           0.07     0.07     0.07
    -------------- -------- -------- --------
    TOTAL DENVER       0.35     0.32     0.33

The total for all of the values for both the `product` and `month` dimensions is `1.00`.